package com.zxy.supplier_system.server.repository

import com.zxy.supplier_system.server.controller.ProcurementSupplierStatisticController
import com.zxy.supplier_system.server.entity.GoodsUnit
import com.zxy.supplier_system.server.entity.Procurement
import com.zxy.supplier_system.server.entity.ProcurementReceipt
import com.zxy.supplier_system.server.entity.ProcurementReceiptItem
import com.zxy.supplier_system.server.entity.ProcurementReturn
import com.zxy.supplier_system.server.entity.ProcurementReturnItem
import com.zxy.supplier_system.server.entity.ProcurementSupplier
import com.zxy.supplier_system.server.entity.SystemService
import com.zxy.supplier_system.server.utils.joinOnce
import jakarta.persistence.EntityManager
import jakarta.persistence.PersistenceContext
import jakarta.persistence.Tuple
import jakarta.persistence.criteria.JoinType
import org.springframework.data.jpa.domain.Specification
import org.springframework.stereotype.Repository
import java.math.BigDecimal
import java.sql.Timestamp
import java.time.OffsetDateTime
import java.time.ZoneId

interface ProcurementSupplierStatisticRepository {

    fun getTotalPurchasePriceGroupByMonth(
        supplierId: Int,
        timezone: String,
        systemService: SystemService,
        request: ProcurementSupplierStatisticController.ProcurementPurchasePriceGroupByYearMonthRequest
    ): List<YearMonthAndPrice>

    data class YearMonthAndPrice(
        val price: BigDecimal,
        val year: Int,
        val month: Int,
    )

    fun getTotalReturnPriceGroupByMonth(supplierId: Int,
                                        timezone: String,
                                        systemService: SystemService,
                                        request: ProcurementSupplierStatisticController.ProcurementPurchasePriceGroupByYearMonthRequest):List<YearMonthAndPrice>

}

@Repository
class ProcurementSupplierStatisticRepositoryImpl : ProcurementSupplierStatisticRepository {

    @PersistenceContext
    private lateinit var entityManager: EntityManager
    override fun getTotalPurchasePriceGroupByMonth(
        supplierId: Int,
        timezone: String,
        systemService: SystemService,
        request: ProcurementSupplierStatisticController.ProcurementPurchasePriceGroupByYearMonthRequest
    ): List<ProcurementSupplierStatisticRepository.YearMonthAndPrice> {
        val criteriaBuilder = entityManager.criteriaBuilder
        val query = criteriaBuilder.createQuery(Tuple::class.java)
        val root = query.from(ProcurementReceiptItem::class.java)
        val procurementReceiptJoin = root.joinOnce<ProcurementReceiptItem, ProcurementReceipt>("procurementReceipt")
        val procurementJoin = procurementReceiptJoin.joinOnce<ProcurementReceipt, Procurement>("procurement")
        val supplierJoin = procurementReceiptJoin.joinOnce<ProcurementReceipt, ProcurementSupplier>("supplier")
        query.where(
            criteriaBuilder.equal(supplierJoin.get<Int>("id"), supplierId),
            Specification.allOf(
                ProcurementReceiptItemRepository.Specifications.createdDateTimeLessThanOrEqual(request.endYearMonth),
                ProcurementReceiptItemRepository.Specifications.createdDateTimeGreatThanOrEqual(request.startYearMonth),
                ProcurementReceiptItemRepository.Specifications.systemServiceEquals(systemService),
                ProcurementReceiptItemRepository.Specifications.procurementValidated(),
            )
                .toPredicate(root, query, criteriaBuilder),
        )
        val unitJoin = root.joinOnce<ProcurementReceiptItem, GoodsUnit>("unit", JoinType.LEFT)
        val amountWithBasicUnit = criteriaBuilder.prod(
            criteriaBuilder.coalesce(root.get<Int>("practicalAmount"),0),
            criteriaBuilder.function(
                "greatest",
                Int::class.java,
                criteriaBuilder.coalesce(unitJoin.get<Int>("basicMultiple"), 1),
                criteriaBuilder.literal(1)
            ),
        )
        val createdDateTime = criteriaBuilder.function(
            "convert_tz",
            Timestamp::class.java,
            procurementJoin.get<OffsetDateTime>("createdDateTime"),
            criteriaBuilder.literal(ZoneId.of("UTC")),
            criteriaBuilder.literal(timezone),
        )
        val year = criteriaBuilder.function("year", Int::class.java, createdDateTime)
        val month = criteriaBuilder.function("month", Int::class.java, createdDateTime)

        query.multiselect(
            criteriaBuilder.sum<BigDecimal>(
                criteriaBuilder.prod(
                    root.get<BigDecimal>("purchasePrice"), amountWithBasicUnit.`as`(
                        BigDecimal::class.java
                    )
                )
            ),
            year,
            month,
        )
        query.groupBy(year,month)
        query.orderBy(criteriaBuilder.asc(year),criteriaBuilder.asc(month))
        val resultList = entityManager.createQuery(query).resultList
        return resultList.map {
            ProcurementSupplierStatisticRepository.YearMonthAndPrice(it.get(0) as BigDecimal,it.get(1) as Int,it.get(2) as Int)
        }
    }

    override fun getTotalReturnPriceGroupByMonth(
        supplierId: Int,
        timezone: String,
        systemService: SystemService,
        request: ProcurementSupplierStatisticController.ProcurementPurchasePriceGroupByYearMonthRequest
    ): List<ProcurementSupplierStatisticRepository.YearMonthAndPrice> {
        val criteriaBuilder = entityManager.criteriaBuilder
        val query = criteriaBuilder.createQuery(Tuple::class.java)
        val root = query.from(ProcurementReturnItem::class.java)
        val procurementReturnJoin = root.joinOnce<ProcurementReturnItem, ProcurementReturn>("procurementReturn")
        val supplierJoin = procurementReturnJoin.joinOnce<ProcurementReturn, ProcurementSupplier>("supplier")
        query.where(
            criteriaBuilder.equal(supplierJoin.get<Int>("id"), supplierId),
            Specification.allOf(
                ProcurementReturnItemRepository.Specifications.createdDateTimeLessThanOrEqual(request.endYearMonth),
                ProcurementReturnItemRepository.Specifications.createdDateTimeGreatThanOrEqual(request.startYearMonth),
                ProcurementReturnItemRepository.Specifications.systemServiceEquals(systemService),
            )
                .toPredicate(root, query, criteriaBuilder),
        )

        val createdDateTime = criteriaBuilder.function(
            "convert_tz",
            Timestamp::class.java,
            procurementReturnJoin.get<OffsetDateTime>("createdDateTime"),
            criteriaBuilder.literal(ZoneId.of("UTC")),
            criteriaBuilder.literal(timezone),
        )
        val year = criteriaBuilder.function("year", Int::class.java, createdDateTime)
        val month = criteriaBuilder.function("month", Int::class.java, createdDateTime)

        query.multiselect(
            criteriaBuilder.sum<BigDecimal>(
                criteriaBuilder.prod(
                    root.get<BigDecimal>("price"), root.get<Int>("amount").`as`(
                        BigDecimal::class.java
                    )
                )
            ),
            year,
            month,
        )
        query.groupBy(year,month)
        query.orderBy(criteriaBuilder.asc(year),criteriaBuilder.asc(month))
        val resultList = entityManager.createQuery(query).resultList
        return resultList.map {
            ProcurementSupplierStatisticRepository.YearMonthAndPrice(it.get(0) as BigDecimal,it.get(1) as Int,it.get(2) as Int)
        }
    }


}